// 导入excel转换为luckysheet能接受的json
import * as Excel from "@deepfos/exceljs";
import { isObject, isNil } from "lodash";
import { getRowHeightPixel, ObjKeyValueTransform } from "../utils/util";
import { excelColorIndexMap, lineStyleLuckyToExceljs } from "./stylesMap";

const transformExcelToLuckyJson = (file) => {
    return new Promise((resolve, reject) => {
        let name = file.name;
        let suffixArr = name.split("."), suffix = suffixArr[suffixArr.length - 1];
        if (suffix !== "xlsx") {
            reject(new Error("Currently only supports the import of xlsx files"));
        }
        if (file.type !== 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
            reject(new Error("请导入xlsx文件!"));
        }
        const buffer = new FileReader();
        buffer.readAsArrayBuffer(file);
        buffer.onload = (e) => {
            transformExcelToLuckyByBuffer(buffer.result, (exportJson, luckysheetfile) => {
                if (exportJson.sheets == null || exportJson.sheets.length == 0) {
                    reject(new Error("Failed to read the content of the excel file, currently does not support xls files!"));
                } else {
                    resolve(exportJson);
                }
            });
        };
    });
};

function transformExcelToLuckyByBuffer (buffer, callBack) {
    let sheets = [];
    let workbook = new Excel.Workbook();
    console.time('ExcelJS读取文件');
    workbook.xlsx.load(buffer).then(() => {
        console.timeEnd('ExcelJS读取文件');
        console.time('ExcelJS数据转为LuckySheet数据');
        const { themesColor, sysClr } = parseThemesXml(workbook);
        const workbookExcelColorIndexMap = {
            ...excelColorIndexMap,
            64: sysClr.fg,
            65: sysClr.bg
        };
        const date1904 = workbook.properties.date1904;
        workbook.eachSheet((sheet, id) => {
            let sheetout = {};
            sheetout.celldata = [];
            sheetout.name = sheet.name;
            sheetout.index = sheet.id;
            sheetout.row = sheet.rowCount < 100 ? 100 : sheet.rowCount;
            sheetout.column = sheet.columnCount < 26 ? 26 : sheet.columnCount;
            sheetout.preRow = sheetout.row;
            sheetout.preColumn = sheetout.column;
            sheetout.status = sheet.state;
            sheetout.calcChain = [];
            sheetout.config = {
                "merge": {}, // 合并单元格
                "rowlen": {}, // 表格行高
                "columnlen": {}, // 表格列宽
                "rowhidden": {}, // 隐藏行
                "colhidden": {}, // 隐藏列
                "borderInfo": [], // 边框
                "authority": {} // 工作表保护
            };

            setMerge(sheet, sheetout);
            setFrozen(sheet, sheetout);

            let maxRow = 0;
            let maxColumn = 0;
            let columnInit = {};    // key: colNumber,value: isInit
            const tType = {
                2: 'n', // 数字类型
                3: 's', // 文本类型
                4: 'd'
            };
            sheet.eachRow({ includeEmpty: true }, (row, rowNum) => {
                maxRow = Math.max(rowNum, maxRow);
                const rowIndex = rowNum - 1;
                row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
                    maxColumn = Math.max(colNumber, maxColumn);
                    let colIndex = colNumber - 1;
                    if (!isNil(cell.value) || !isNil(cell.formula) || Object.keys(cell.style || {}).length) {
                        const {
                            style: { font = {}, fill = {} },
                            alignment = {}
                        } = cell;
                        const r = cell.row - 1;
                        const c = cell.col - 1;
                        let value = {}, ct, f;
                        if (!isNil(cell.formula)) {
                            if (cell.formula.toUpperCase().includes('DFDATA(')) {
                                value = {
                                    v: '#待查询',
                                    m: '#待查询'
                                };
                            } else {
                                if (cell.result && isObject(cell.result)) {
                                    value = {
                                        v: cell.result.error,
                                        m: cell.result.error
                                    };
                                } else {
                                    value = {
                                        v: cell.result,
                                        m: cell.result
                                    };
                                }
                            }
                            f = cell.formula;
                            f = f.charAt(0) === '=' ? f : `=${f}`;
                            sheetout.calcChain.push({
                                r: cell.row - 1,
                                c: cell.col - 1,
                                index: sheetout.index
                            });
                        } else if (cell.value && cell.value.richText) {
                            value = {
                                v: cell.value.richText.map(item => item.text || '').join('')
                            };
                        } else if (cell.value instanceof Date) {
                            value = {
                                v: cell.originValue + (date1904 ? 1462 : 0)
                            };
                        } else {
                            value = {
                                v: cell.value
                            };
                        }

                        if (cell.numFmt) {
                            // 公式类型
                            ct = {
                                fa: cell.numFmt,
                                t: tType[cell.type] || 'n'
                            };

                            if (ct.t === 'n' && cell.numFmt.includes('0.0,')) {
                                ct.fa = `w${cell.numFmt.replace('0.0,', '0.0')}`;
                            }
                            if (ct.t === 'n' && cell.numFmt.includes('0.00,,')) {
                                ct.fa = `y${cell.numFmt.replace('0.00,,', '0.00')}`;
                            }
                        }

                        ct && (value.ct = ct);     // 格式
                        f && (value.f = f);    // 公式

                        const vtAlign = getAlign(alignment.vertical || 'bottom'); // 怀疑exceljs bug，bottom传的是undefined
                        const htAlign = getAlign(alignment.horizontal);
                        const indent = alignment.indent;

                        const cellData = {
                            r,
                            c,
                            v: {
                                indent,
                                tb: alignment.wrapText ? 2 : 1, // 2 自动换行 1 溢出
                                ...generateFontStyle(font, themesColor, workbookExcelColorIndexMap),
                                ...value
                            }
                        };

                        fill?.fgColor && (cellData.v.bg = getColor(fill.fgColor, themesColor, workbookExcelColorIndexMap, '#fff')); // 背景色
                        !isNil(vtAlign) && (cellData.v.vt = vtAlign);   // 垂直对齐
                        !isNil(htAlign) && (cellData.v.ht = htAlign);   // 水平对齐
                        sheetout.celldata.push(cellData);

                        // 设置边框
                        setBorder(cell.border, cellData.r, cellData.c, themesColor, workbookExcelColorIndexMap, sheetout);
                    }

                    // 导入列宽,隐藏
                    if (!columnInit[colNumber]) {
                        columnInit[colNumber] = true;
                        const col = sheet.getColumn(colNumber);
                        if (col.hidden) sheetout.config.colhidden[colIndex] = 0;
                        sheetout.config.columnlen[colIndex] = (col.width || 8.43) * 64 / 8.43;
                    }
                });

                // 导入行高,隐藏
                sheetout.config.rowlen[rowIndex] = getRowHeightPixel(row.height) || 20;
                if (row.hidden) sheetout.config.rowhidden[rowIndex] = 0;
            });
            sheets.push(sheetout);
        });
        callBack({
            sheets,
            info: {
                title: workbook.title,
                creator: workbook.creator
            }
        });
        console.timeEnd('ExcelJS数据转为LuckySheet数据');
    });
}

/**
 * 生成字体相关样式
 *
 */
function generateFontStyle (font, themesColor, excelColorIndexMap) {
    let obj = {};
    !isNil(font.name) && (obj.ff = font.name);
    !isNil(font.size) && (obj.fs = font.size);
    !isNil(font.bold) && (obj.bl = Number(font.bold));
    !isNil(font.italic) && (obj.it = Number(font.italic));
    !isNil(font.color) && (obj.fc = getColor(font.color, themesColor, excelColorIndexMap, '#000'));
    !isNil(font.underline) && (obj.un = Number(font.underline));
    !isNil(font.strike) && (obj.cl = Number(font.strike));

    return obj;
}

const lineStyleExcelJsToLucky = ObjKeyValueTransform(lineStyleLuckyToExceljs);

/**
 * 按单元格设置边框
 */
function setBorder (border, r, c, themesColor, excelColorIndexMap, sheetout) {
    if (isNil(border)) return;

    let borderInfo = {
        "rangeType": "cell",
        "value": {
            "row_index": r,
            "col_index": c
        }
    };

    let flag = false;
    for (let [pos, item] of Object.entries(border)) {
        flag = true;

        borderInfo.value[pos[0]] = {
            style: parseInt(lineStyleExcelJsToLucky[item.style]),
            color: getColor(item.color, themesColor, excelColorIndexMap, '#000')
        };
    }

    flag && sheetout.config.borderInfo.push(borderInfo);
}

/**
 * 合并单元格
 * @param sheet
 */
function setMerge (sheet, sheetout) {
    let merge = {};
    Object.keys(sheet._merges).forEach((key) => {
        const { model: { top, left, bottom, right } } = sheet._merges[key];
        merge[`${top - 1}_${left - 1}`] = {
            r: top - 1,
            c: left - 1,
            rs: bottom - top + 1,
            cs: right - left + 1
        };
    });
    sheetout.config.merge = merge;
}

/**
 * 设置冻结行列
 */
function setFrozen (sheet, sheetout) {
    if (sheet.views && sheet.views[0].state === 'frozen') {
        const { xSplit, ySplit } = sheet.views[0];
        let type;
        if (xSplit && ySplit) {
            type = 'rangeBoth';
        } else if (!xSplit) {
            type = 'rangeRow';
        } else {
            type = 'rangeColumn';
        }
        sheetout.frozen = {
            "type": type,
            "range": {
                "row_focus": ySplit > 0 ? ySplit - 1 : null,
                "column_focus": xSplit > 0 ? xSplit - 1 : null
            }
        };
    }
}

/**
 * 解析themesXml
 */
function parseThemesXml (workbook) {
    // 还没有找到规律，暂时获取workbook._themes的第一个属性，目前大部分数据看key为 themes1
    const themesXml = new DOMParser().parseFromString(workbook._themes[Object.keys(workbook._themes)[0]], 'application/xml');
    const themesColor = [];
    const nodeList = themesXml.querySelector('clrScheme')?.children || [];
    for (let i = 0; i < nodeList.length;) {
        let node = nodeList[i];
        if (/dk/.test(node.localName)) {
            let child = node.querySelector('sysClr,srgbClr');
            themesColor[i + 1] = child.getAttribute('lastClr') || child.getAttribute('val');
            child = nodeList[i + 1].querySelector('sysClr,srgbClr');
            themesColor[i] = child.getAttribute('lastClr') || child.getAttribute('val');
            i = i + 2;
        } else {
            themesColor[i] = node.querySelector('srgbClr').getAttribute('val');
            i++;
        }
    }

    // 查找系统颜色
    let sysClr = {
        fg: '#000000',
        bg: '#ffffff'
    };
    let sysClrList = themesXml.querySelectorAll('clrScheme sysClr') || [];
    for (let node of sysClrList) {
        const type = node.getAttribute('val');
        const clr = node.getAttribute('lastClr');
        if (type == 'windowText' && !isNil(clr)) sysClr.fg = `#${clr}`;
        if (type == 'window' && !isNil(clr)) sysClr.bg = `#${clr}`;
    }

    return {
        themesXml,
        themesColor,
        sysClr
    };
}

function getColor (colorObj, themesColor, excelColorIndexMap, defaultColor) {
    if (!colorObj) return defaultColor;
    if (colorObj.argb) {
        if (colorObj.argb.length === 8) return `#${colorObj.argb.substr(2)}`;
        return  `#${colorObj.argb}`;
    } else if (!isNil(colorObj.indexed)) {
        return excelColorIndexMap[colorObj.indexed] || defaultColor;
    } else if (!isNil(colorObj.theme)) {
        const color = getThemeColor(themesColor, colorObj.theme, colorObj.tint);
        return color ? `#${color}` : defaultColor;
    }

    return defaultColor;
}

function getThemeColor (themesColor, theme, tint) {
    let color = themesColor[theme];
    if (!color) return '';
    if (isNil(tint)) return color;
    let RGBArray = color.match(/(\w{2})(\w{2})(\w{2})/);
    let result = '';
    for (let i = 1, len = RGBArray.length; i < len; i++) {
        // 计算RGB对应的10进制的值
        let num = parseInt(RGBArray[i][0], 16) * 16 + parseInt(RGBArray[i][1], 16);
        // 计算tint后，转为16进制 https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.colortype.tint?view=openxml-2.8.1
        let str;
        if (tint >= 0) {
            str = Math.min(Math.max(Math.round(num * (1.0 - tint) + (255 - 255 * (1.0 - tint))), 0), 255).toString(16);
            // str = Math.min(Math.max(Math.round(num + ((255 - num) * tint)), 0), 255).toString(16);
        } else {
            str = Math.min(Math.max(Math.round(num * (1.0 + tint)), 0), 255).toString(16);
            // str = Math.min(Math.max(Math.round(num * Math.abs(tint)), 0), 255).toString(16);
        }
        result += str.length < 2 ? `0${str}` : str;
    }
    return result;
}

function getAlign (alignType) {
    let result;
    switch (alignType) {
    case 'top':
    case 'left':
        result = 1; break;
    case 'middle':
    case 'center':
        result = 0; break;
    case 'bottom':
    case 'right':
        result = 2;
    }
    return result;
}

export  { transformExcelToLuckyJson, transformExcelToLuckyByBuffer };
